alter table promotion
add SingleUseOnly bit not null default(0),
	Match char(3) not null default('ALL')
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetDeals]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'


	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
									Business.BusinessId, Business.BusinessName,
									STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwPromotionRating.RatingText AS PromotionRating,
									vwBusinessRating.CountReviews AS Reviews,
									vwPromotionRating.CountReviews AS PromotionReviews, 
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.Latitude, Business.Longitude,
									Promotion.PromotionText, Business.[Description], 
									
									Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes,
									ISNULL(vwLike.Dislikes, 0) AS Dislikes,
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									
									ISNULL(vwLikePromotion.Likes, 0) AS PromotionLikes,
									ISNULL(vwLikePromotion.Dislikes, 0) AS PromotionDislikes,
									ISNULL(vwLikePromotion.IsFeedbackLeft, 0) AS PromotionIsFeedbackLeft,
									
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate,
									
									Promotion.SingleUseOnly, Promotion.Match
								FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
									LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
									LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
									LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
									LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
									LEFT JOIN (SELECT BusinessId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM BusinessLike
											GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
									LEFT JOIN (SELECT PromotionId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM PromotionLike
											GROUP BY PromotionId) AS vwLikePromotion ON Promotion.PromotionId = vwLikePromotion.PromotionId
									LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
									LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
									LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
									LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
												FROM PromotionCheckin
												WHERE PromotionCheckin.UserId = @UserId
												GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
								WHERE (PromotionPush.UserId = @UserId)
									AND (bb.UserId IS NULL)
									AND (bp.UserId IS NULL)

									AND (
											((Promotion.ProximityRadiusId not in (12, 13)) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280)))
											-- over 200 miles
											OR ((Promotion.ProximityRadiusId = 13) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) > CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280)))
											-- custom range
											OR ((Promotion.ProximityRadiusId = 12) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, Promotion.CustomRange)))
										)

									AND (Promotion.StatusCode = 'ACT')
									AND (Promotion.Deleted = 0)
									AND (Promotion.Ready = 1)
									AND (Business.IsActive = 1)
									AND (NOT EXISTS(SELECT 1 
													FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
													WHERE pc.PromotionId = Promotion.PromotionId))
									/*AND (EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))*/
									AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 180
									AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
											
											/*(Promotion.BaseCostTimingId <> 5)
											or
											-- Ongoing, Scheduled timing
											(
												CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
												AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
											)
										)*/

								ORDER BY PromotionPush.DatePush DESC
								FOR XML PATH ('Deal'), ROOT('Deals'))
		end
		else
		begin
			SELECT
				Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				ProximityRadius.FromRadius as ProximityFromRadius, ProximityRadius.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], BaseCostTiming.Timing as BaseCostTimingName,
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
				Promotion.SingleUseOnly, Promotion.Match,
				
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
				
				vwBusinessRating.RatingText AS Rating,
				vwPromotionRating.RatingText AS PromotionRating,
				vwBusinessRating.CountReviews AS Reviews,
				vwPromotionRating.CountReviews AS PromotionReviews, 
				
				CONVERT(INT, 0) AS MaxPushes, 
				CONVERT(MONEY, 0) AS SpentMoney, CONVERT(INT, 0) AS SpentPushes, 
				CONVERT(MONEY, 0) AS UnusedMoney, CONVERT(INT, 0) AS UnusedPushes, 
							
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
				
				Picture = null,
				PictureContentType = null
								
			FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
				LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
				LEFT JOIN BaseCostTiming ON Promotion.BaseCostTimingId = BaseCostTiming.BaseCostTimingId
				LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
				LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
				LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
				LEFT JOIN (SELECT PromotionCheckin.PromotionId, COUNT(*) AS CheckIns, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
							FROM PromotionCheckin
							WHERE PromotionCheckin.UserId = @UserId
							GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
				LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
				LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
				/*
				LEFT JOIN (SELECT BusinessId,
							SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
							SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
							CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
						FROM BusinessLike
						GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
				
				LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
				*/
			WHERE (PromotionPush.UserId = @UserId)
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
				AND (Promotion.StatusCode = 'ACT')
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Business.IsActive = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				/*AND (EXISTS(SELECT 1 
							FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
							WHERE pc.PromotionId = Promotion.PromotionId))*/
				AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 180
			ORDER BY PromotionPush.DatePush DESc
			
			return
		end
	end

	if @returnAsDataset = 0	
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt], 
									Promotion.[BaseCostTimingStarts], 
									Promotion.[BaseCostTimingEnds],
									Promotion.BaseCostTimingStartsWithinDay,
									Promotion.BaseCostTimingEndsWithinDay,
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
																		
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
									-- Max pushes - Spent Pushes
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories,
									Promotion.PromotionNumber, Promotion.SingleUseOnly, Promotion.Match
								FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
											left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
											left join Business on Promotion.BusinessId = Business.BusinessId,
									(SELECT COUNT(*) AS CountPushes
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
																											
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
								
			if @returnAsDataset = 1
			begin
				SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
						pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
						Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
						Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
						Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
						
						dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
						dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
						dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
						
						Promotion.[CampaignBudget], Promotion.[Name],
						Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
						Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
						Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
						Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
						Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
						Promotion.SingleUseOnly, Promotion.Match,
						
						ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
												
						vwBusinessRating.RatingText AS Rating,
						vwBusinessRating.CountReviews AS Reviews,
																					
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END AS MaxPushes,
												
						isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
						ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
						ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
												
						-- Max pushes - Spent Pushes
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
												
						case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
						else -1 
						end as MinutesLeft,
						
						Picture = null,
						PictureContentType = null
				FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
							left join Business on Promotion.BusinessId = Business.BusinessId,
							
					(SELECT COUNT(*) AS CountPushes
						FROM PromotionPush
						WHERE PromotionId = @PromotionId) AS vwPushes,
														
					(SELECT COUNT(*) AS CheckIns 
						FROM PromotionCheckin 
						WHERE PromotionId = @PromotionId) AS vwCheckin								
				WHERE (Promotion.PromotionId = @PromotionId)
				
				SELECT Category.CategoryId, Category.CategoryName,
						dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
				FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
				WHERE PromotionCategory.PromotionId = @PromotionId
				
				return
			end
		END								
		ELSE
			SET @IdResult = -1

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotions]
(
	@Token VARCHAR(255),
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'
	
	IF @IdResult = 1
	begin
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.BusinessId, 
							Promotion.ProximityRadiusId, Promotion.CustomRange, Promotion.Over200MilesFlag, 
							Promotion.RegisteredOutsideOfZip, Promotion.RegisteredOutsideOfState, Promotion.RegisteredOutsideOfCity, 
							Promotion.BaseCostTimingId, 
							
							Promotion.[BaseCostTimingScheduledAt], 
							Promotion.[BaseCostTimingStarts], 
							Promotion.[BaseCostTimingEnds],
							
							Promotion.CampaignBudget, Promotion.Name, Promotion.PromotionText, 
							Promotion.PromixityRadiusFixedCost, Promotion.PromixityRadiusRatePerPush, 
							Promotion.CostTimingFixedCost, Promotion.CostTimingRatePerPush, 
							Promotion.FixedCost, Promotion.RatePerPush, 
							Promotion.CheckinCode, Promotion.Deleted, Promotion.Ready,
							coalesce(vwPush.PushesCount, 0) as PushesCount,
							case
								when Promotion.BaseCostTimingId <> 5 then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1
									) then Promotion.StatusCode
								else 'PEN' end as StatusCode,
							case
								when Promotion.BaseCostTimingId <> 5 then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1
									) then PromotionStatus.Name
								else 'Pending' end as PromotionStatus,
							
							vwBusinessRating.RatingText AS Rating,
							vwBusinessRating.CountReviews AS Reviews,
							Promotion.PromotionNumber, Promotion.SingleUseOnly, Promotion.Match
									
							FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
											LEFT JOIN (SELECT PromotionId, COUNT(*) AS PushesCount
													FROM PromotionPush
													GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
		if @returnAsDataset = 1
		begin
			SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
				
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
				
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
				Promotion.SingleUseOnly, Promotion.Match,
			
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
				vwBusinessRating.RatingText AS Rating,
				vwBusinessRating.CountReviews AS Reviews,
																		
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END AS MaxPushes,
									
				isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
				ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
				ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
				-- Max pushes - Spent Pushes
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
			
				Picture = null,
				PictureContentType = null
			FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
							LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin 
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
							LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
			WHERE (Promotion.BusinessId = @BusinessId)
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)		
				
			RETURN
		END
	END
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@SummaPay money,
	@SummaRest money,
	
	@SingleUseOnly bit = 0,
	@Match char(3) = 'ALL'
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			if not exists (select 1 from PromotionCategory (nolock) where PromotionId = @PromotionId)
			begin
				set @IdResult = -2
				goto ExitWithResult
			end
			
			SELECT @CampaignBudget = ISNULL(CampaignBudget, 0)
			FROM Promotion (NOLOCK)
			WHERE (PromotionId = @PromotionId)
			
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
			
			begin transaction
				UPDATE Promotion
				SET
					Ready = 1,
					PromotionNumber = (select isnull(max(PromotionNumber) + 1, 1) from Promotion (nolock) WHERE BusinessId = @BusinessId),
					CheckinCode = CONVERT (VARCHAR(4), (1000+ ABS(CHECKSUM(NewId())) % 8999)),
					SingleUseOnly = @SingleUseOnly, Match = @Match
				WHERE (PromotionId = @PromotionId)
				
				-- open promotion account
				exec spAccount_Add @BusinessId = @BusinessId, @PromotionId = @PromotionId
				
				IF @CampaignBudget > 0
					exec spAccountTransaction_PromotionPay @BusinessId = @BusinessId, @PromotionId = @PromotionId, @SummaPay = @SummaPay, @SummaRest = @SummaRest, @CreditCardNo = @CreditCardNo
			
				exec spPromotion_StartStop @PromotionId = @PromotionId
			commit transaction
		END	
		ELSE
			SET @IdResult = -1

ExitWithResult:
	SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spAPI_User_GetAllActivePromotionsByBusiness]
(
	@Token VARCHAR(255),
	@businessId int,
	@Latitude float = null,
	@Longitude float = null,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'

	
	DECLARE @IdResult INT
	
	DECLARE @Result XML
	declare @userId int
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @userId = @userId out
	
	IF @IdResult = 1
	begin
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
								Promotion.SingleUseOnly, Promotion.Match,
								Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								0 AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours,
								ISNULL(vwLike.Likes, 0) AS Likes,
								ISNULL(vwLike.Dislikes, 0) AS Dislikes,
								ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
								
								case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
								vwCheckin.LastCheckinDate
							FROM Promotion
								LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
								LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
								LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
								LEFT JOIN (SELECT BusinessId,
											SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
											SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
											CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
										FROM BusinessLike
										GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
								LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
								LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
								LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
								LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
											FROM PromotionCheckin
											WHERE PromotionCheckin.UserId = @UserId
											GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (bb.UserId IS NULL)
								AND (bp.UserId IS NULL)
								/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
								--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
								AND (Promotion.StatusCode = 'ACT')
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
								AND (Business.IsActive = 1)
								AND (NOT EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))
								AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
										
										/*(Promotion.BaseCostTimingId <> 5)
										or
										-- Ongoing, Scheduled timing
										(
											CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
											AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
										)
									)	*/											
							FOR XML PATH ('Deal'), ROOT('Deals'))
							
		if @returnAsDataset = 1
		begin
			SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
				
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
				dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
				
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
				Promotion.SingleUseOnly, Promotion.Match,
			
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
				vwBusinessRating.RatingText AS Rating,
				vwBusinessRating.CountReviews AS Reviews,
																		
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END AS MaxPushes,
									
				isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
				ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
				ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
				-- Max pushes - Spent Pushes
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
			
				Picture = null,
				PictureContentType = null
			FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
							LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin 
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
							LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
			WHERE (Promotion.BusinessId = @BusinessId)
				AND (Promotion.StatusCode = 'ACT')
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
							Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
				
			RETURN		
		end
	end
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spPromotion_GetById]
(
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
			
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			Promotion.SingleUseOnly, Promotion.Match,
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
						
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Promotion.Picture,
			Promotion.PictureContentType
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
					left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
					left join Business on Promotion.BusinessId = Business.BusinessId,
											
					/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
					FROM PromotionPush
					WHERE PromotionId = @PromotionId) AS vwPushes,
									
					(SELECT SUM(Amount) AS Amount
					FROM PromotionPay
					WHERE PromotionId = @PromotionId) AS vwPay,
									
					(SELECT SUM(FixedCost) AS Amount
					FROM PromotionCost
					WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
																		
					(SELECT COUNT(*) AS CountPushes
					FROM PromotionPush
					WHERE PromotionId = @PromotionId) AS vwPushes,
					
					(SELECT COUNT(*) AS CheckIns 
					FROM PromotionCheckin 
					WHERE PromotionId = @PromotionId) AS vwCheckin
												
	WHERE (Promotion.PromotionId = @PromotionId)
END
go
ALTER PROCEDURE [dbo].[spAPI_User_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataSet bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult != 1
	begin
		SET @IdResult = -1
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
		return
	end
	
	IF not EXISTS(SELECT 1 FROM PromotionPush WHERE UserId = @UserId AND PromotionId = @PromotionId)
	begin
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
		return
	end

	if @returnAsDataSet = 0
	begin	
		SELECT @Result = (SELECT 
								Promotion.[PromotionId],
								Promotion.[BusinessId],
								Promotion.[ProximityRadiusId],
								pr.FromRadius as ProximityFromRadius,
								pr.ToRadius as ProximityToRadius,
								Promotion.[CustomRange],
								Promotion.[Over200MilesFlag],
								Promotion.[RegisteredOutsideOfZip],
								Promotion.[RegisteredOutsideOfState],
								Promotion.[RegisteredOutsideOfCity],
								Promotion.[BaseCostTimingId],
								bct.Timing as BaseCostTimingName,
								
								Promotion.[BaseCostTimingScheduledAt], 
								Promotion.[BaseCostTimingStarts], 
								Promotion.[BaseCostTimingEnds],
								
								Promotion.[CampaignBudget],
								Promotion.[Name],
								Promotion.[PromotionText],
								Promotion.[PromixityRadiusFixedCost],
								Promotion.[PromixityRadiusRatePerPush],									
								Promotion.[CostTimingFixedCost],
								Promotion.[CostTimingRatePerPush],
								Promotion.BaseCostTimingRatePerPush,
								Promotion.BaseCostTimingFixedCost,
								Promotion.[StatusCode],
								Promotion.[FixedCost],
								Promotion.[RatePerPush],
								Promotion.[CheckinCode],
								Promotion.[Deleted],
								Promotion.[Ready],	
								Promotion.SingleUseOnly, Promotion.Match,								
								ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
								CASE WHEN (Promotion.RatePerPush = 0) THEN 0
								ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
								END AS MaxPushes,
								
								--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
								isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
								ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
								--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
								ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
																
								-- Max pushes - Spent Pushes
								CASE WHEN (Promotion.RatePerPush = 0) THEN 0
								ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
								END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
								
								case
									when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
									else -1 end as MinutesLeft,
									
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
										dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
									FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
									WHERE PromotionCategory.PromotionId = Promotion.PromotionId
									FOR XML AUTO, TYPE) AS Categories
							FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
											left join Business on Promotion.BusinessId = Business.BusinessId,
								/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT SUM(Amount) AS Amount
								FROM PromotionPay
								WHERE PromotionId = @PromotionId) AS vwPay,
								
								(SELECT SUM(FixedCost) AS Amount
								FROM PromotionCost
								WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
								
								
								(SELECT COUNT(*) AS CountPushes
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT COUNT(*) AS CheckIns 
								FROM PromotionCheckin 
								WHERE PromotionId = @PromotionId) AS vwCheckin
											
							WHERE (Promotion.PromotionId = @PromotionId)
							FOR XML PATH ('Promotion'))

		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
	end
	else
	begin
		SELECT 
			Promotion.[PromotionId],
			Promotion.[BusinessId],
			Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius,
			pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange],
			Promotion.[Over200MilesFlag],
			Promotion.[RegisteredOutsideOfZip],
			Promotion.[RegisteredOutsideOfState],
			Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId],
			bct.Timing as BaseCostTimingName,
			
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
			
			Promotion.[CampaignBudget],
			Promotion.[Name],
			Promotion.[PromotionText],
			Promotion.[PromixityRadiusFixedCost],
			Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost],
			Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush,
			Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode],
			Promotion.[FixedCost],
			Promotion.[RatePerPush],
			Promotion.[CheckinCode],
			Promotion.[Deleted],
			Promotion.[Ready],		
			Promotion.SingleUseOnly, Promotion.Match,							
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
			
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
			
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
			
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
			
			case
				when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
				else -1 end as MinutesLeft
		FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
						left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
						left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
						left join Business on Promotion.BusinessId = Business.BusinessId,
								/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT SUM(Amount) AS Amount
								FROM PromotionPay
								WHERE PromotionId = @PromotionId) AS vwPay,
								
								(SELECT SUM(FixedCost) AS Amount
								FROM PromotionCost
								WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
								
								
			(SELECT COUNT(*) AS CountPushes
			FROM PromotionPush
			WHERE PromotionId = @PromotionId) AS vwPushes,
			
			(SELECT COUNT(*) AS CheckIns 
			FROM PromotionCheckin 
			WHERE PromotionId = @PromotionId) AS vwCheckin
						
		WHERE (Promotion.PromotionId = @PromotionId)
		
		SELECT
			Category.CategoryId, Category.CategoryName,
			dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
		FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
		WHERE PromotionCategory.PromotionId = @PromotionId
	end	
END
go
ALTER PROCEDURE [dbo].[spPromotion_GetList]
(
	@BusinessId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingScheduledAt]) as BaseCostTimingScheduledAt, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingStarts]) as BaseCostTimingStarts, 
			dateadd(hour, Business.Gmt, Promotion.[BaseCostTimingEnds]) as BaseCostTimingEnds,
			
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			Promotion.SingleUseOnly, Promotion.Match,							
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
			
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Picture = null,
			PictureContentType = null
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
					left join Business on Promotion.BusinessId = Business.BusinessId
					/*LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(Amount) AS Amount
								FROM PromotionPay
								GROUP BY PromotionId) AS vwPay ON Promotion.PromotionId = vwPay.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
								FROM PromotionCost
								GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId*/
					left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
								FROM PromotionPush
								GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId			
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
								FROM PromotionCheckin 
								GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
END
go